import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from sqlalchemy import create_engine

# 设置中文字体和图片清晰度
plt.rcParams["font.family"] = ["SimHei", "Heiti TC"]
# plt.rcParams['figure.dpi'] = 300

# PostgreSQL 配置
postgres_host = "172.16.137.39"  
postgres_port = "5432"  
postgres_user = "a2513220218"  
postgres_password = "xawl-6043"  
postgres_database = "a2513220218" 
postgres_schema = "datawarehouse" 

# 建立数据库连接
conn_string = f"postgresql://{postgres_user}:{postgres_password}@{postgres_host}:{postgres_port}/{postgres_database}"
engine = create_engine(conn_string)
conn = engine.connect()

# 从数据库查询数据的函数
def query_data_from_database():
    """从数据库查询三个表的数据"""
    # 客户表查询
    query_customer = f"""
    SELECT * FROM {postgres_schema}.dim_customer;
    """
    dim_customer = pd.read_sql(query_customer, conn)
    
    # 产品表查询
    query_product = f"""
    SELECT * FROM {postgres_schema}.dim_product;
    """
    dim_product = pd.read_sql(query_product, conn)
    
    # 销售表查询
    query_sales = f"""
    SELECT * FROM {postgres_schema}.fact_sales;
    """
    fact_sales = pd.read_sql(query_sales, conn)
    
    return dim_customer, dim_product, fact_sales

def plot_product_category_kpi(dim_product, fact_sales):
    # 1. 合并表：产品表 + 销售表（计算销售额、利润）
    merged = pd.merge(fact_sales, dim_product, on='prd_key', how='left')
    merged['revenue'] = merged['sls_quantity'] * merged['sls_price']  # 销售额
    merged['profit'] = merged['revenue'] - (merged['sls_quantity'] * merged['prd_cost'])  # 利润（简化版）
    
    # 2. 按产品类别聚合核心指标
    category_kpi = merged.groupby('prd_category').agg({
        'sls_quantity': 'sum',    # 总销量
        'revenue': 'sum',         # 总销售额
        'prd_cost': 'mean',       # 平均成本
        'profit': 'sum'           # 总利润
    }).reset_index()
    
    # 3. 可视化：多指标对比（用子图分开展示）
    fig, axes = plt.subplots(2, 2, figsize=(12, 8))
    axes = axes.flatten()  # 转成一维数组方便遍历
    
    # 3.1 销量对比
    sns.barplot(x='prd_category', y='sls_quantity', data=category_kpi, ax=axes[0])
    axes[0].set_title('产品类别 - 总销量')
    
    # 3.2 销售额对比
    sns.barplot(x='prd_category', y='revenue', data=category_kpi, ax=axes[1])
    axes[1].set_title('产品类别 - 总销售额')
    
    # 3.3 平均成本对比
    sns.barplot(x='prd_category', y='prd_cost', data=category_kpi, ax=axes[2])
    axes[2].set_title('产品类别 - 平均成本')
    
    # 3.4 总利润对比
    sns.barplot(x='prd_category', y='profit', data=category_kpi, ax=axes[3])
    axes[3].set_title('产品类别 - 总利润')
    
    plt.tight_layout()
    plt.show()

def plot_product_cost_segment(dim_product, fact_sales):
    # 1. 合并表 + 计算销量
    merged = pd.merge(fact_sales, dim_product, on='prd_key', how='left')
    product_sales = merged.groupby('prd_key').agg({
        'sls_quantity': 'sum',  # 每个产品的总销量
        'prd_cost': 'mean'      # 产品成本
    }).reset_index()
    
    # 2. 成本区间切片（示例：分 3 层，可自定义）
    product_sales['cost_segment'] = pd.qcut(
        product_sales['prd_cost'], 
        q=[0, 0.3, 0.7, 1], 
        labels=['低成本', '中成本', '高成本']
    )
    
    # 3. 按成本区间聚合销量
    cost_segment_sales = product_sales.groupby('cost_segment')['sls_quantity'].sum().reset_index()
    
    # 4. 可视化：成本区间 vs 总销量
    plt.figure(figsize=(8, 5))
    sns.barplot(
        x='cost_segment', 
        y='sls_quantity', 
        data=cost_segment_sales, 
        order=['低成本', '中成本', '高成本']
    )
    plt.title('产品成本区间的销量分布')
    plt.xlabel('成本区间')
    plt.ylabel('总销量')
    plt.tight_layout()
    plt.show()

def plot_product_monthly_trend(dim_product, fact_sales):
    # 1. 合并表 + 处理日期
    merged = pd.merge(fact_sales, dim_product, on='prd_key', how='left')
    merged['order_month'] = pd.to_datetime(merged['sls_order_date']).dt.to_period('M')
    merged['revenue'] = merged['sls_quantity'] * merged['sls_price']  # 销售额
    
    # 2. 按 产品类别 + 月份 聚合销售额
    monthly_trend = merged.groupby(['prd_category', 'order_month'])['revenue'].sum().reset_index()
    monthly_trend['order_month'] = monthly_trend['order_month'].astype(str)  # 转字符串方便画图
    
    # 3. 可视化：多产品月度趋势对比
    plt.figure(figsize=(14, 7))
    sns.lineplot(
        x='order_month', 
        y='revenue', 
        hue='prd_category', 
        data=monthly_trend,
        marker='o'
    )
    plt.title('产品类别月度销售额趋势')
    plt.xlabel('月份')
    plt.ylabel('总销售额')
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.show()

def plot_product_country_preference(dim_product, fact_sales, dim_customer):
    # 1. 合并三表：产品 + 销售 + 客户
    merged = pd.merge(fact_sales, dim_product, on='prd_key', how='left')
    merged = pd.merge(merged, dim_customer, on='cust_num', how='left')
    
    # 2. 按 国家 + 产品类别 聚合销量
    country_preference = merged.groupby(['cust_country', 'prd_category'])['sls_quantity'].sum().reset_index()
    
    # 3. 可视化：热力图（国家×产品类别）
    heatmap_data = country_preference.pivot_table(
        index='cust_country', 
        columns='prd_category', 
        values='sls_quantity', 
        aggfunc='sum',
        fill_value=0
    )
    
    plt.figure()
    sns.heatmap(heatmap_data, annot=True, fmt='.0f', cmap='Greens')
    plt.title('不同国家的产品类别偏好热力图')
    plt.xlabel('产品类别')
    plt.ylabel('客户国家')
    plt.tight_layout()
    plt.show()

def plot_product_lifecycle(dim_product, fact_sales):
    # （注意：需确保产品表有 `prd_launch_date` 字段，若没有可模拟或补充）
    # 1. 模拟产品上市日期（实际项目中替换为真实字段）
    dim_product['prd_launch_date'] = pd.to_datetime('2023-01-01') + pd.DateOffset(days=dim_product.index % 365)
    
    # 2. 定义新品：上市时间 < 1 年（可自定义周期）
    dim_product['lifecycle'] = dim_product['prd_launch_date'].apply(
        lambda x: '新品' if (pd.Timestamp.now() - x).days < 365 else '老品'
    )
    
    # 3. 合并表 + 计算销量
    merged = pd.merge(fact_sales, dim_product, on='prd_key', how='left')
    lifecycle_sales = merged.groupby('lifecycle')['sls_quantity'].sum().reset_index()
    
    # 4. 可视化：新品 vs 老品销量对比
    plt.figure()
    sns.barplot(
        x='lifecycle', 
        y='sls_quantity', 
        data=lifecycle_sales, 
        order=['新品', '老品']
    )
    plt.title('产品生命周期 - 销量对比')
    plt.xlabel('生命周期阶段')
    plt.ylabel('总销量')
    plt.tight_layout()
    plt.show()

def main():
    try:
        # 1. 查数据
        dim_customer, dim_product, fact_sales = query_data_from_database()
        
        # 3. 新增产品维度切片分析
        plot_product_category_kpi(dim_product, fact_sales)
        plot_product_cost_segment(dim_product, fact_sales)
        plot_product_monthly_trend(dim_product, fact_sales)
        plot_product_country_preference(dim_product, fact_sales, dim_customer)
        plot_product_lifecycle(dim_product, fact_sales)  # 需产品表有上市日期字段
        
        print("可视化完成")
        
    except Exception as e:
        print(f"执行主程序时出错: {str(e)}")
    finally:
        # 关连接
        if conn:
            conn.close()
        if engine:
            engine.dispose()
        print("已关闭数据库连接")

# 修改主程序入口
if __name__ == "__main__":
    main()